﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DTcms.JOB.users.Helper;
using DTcms.Common;

namespace DTcms.JOB.member_order.DAL
{
	/// <summary>
	/// 数据访问类:ch_member_order
	/// </summary>
	public partial class member_order
	{
		public member_order()
		{ }
		#region 基本方法============================
		/// <summary>
		/// 是否存在该记录
		/// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from ch_member_order");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
		
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.member_order model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ch_member_order(");
            strSql.Append("order_no,user_id,group_id,amount,add_time,payment_id,payment_status,payment_time,trade_no,remark,parent_mobile)");
            strSql.Append(" values (");
            strSql.Append("@order_no,@user_id,@group_id,@amount,@add_time,@payment_id,@payment_status,@payment_time,@trade_no,@remark,@parent_mobile)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@order_no", SqlDbType.NVarChar,50),
					new SqlParameter("@user_id", SqlDbType.Int,4),
					new SqlParameter("@group_id", SqlDbType.Int,4),
					new SqlParameter("@amount", SqlDbType.Decimal,5),
					new SqlParameter("@add_time", SqlDbType.DateTime),
					new SqlParameter("@payment_id", SqlDbType.TinyInt,1),
					new SqlParameter("@payment_status", SqlDbType.TinyInt,1),
					new SqlParameter("@payment_time", SqlDbType.DateTime),
					new SqlParameter("@trade_no", SqlDbType.NVarChar,100),
                    new SqlParameter("@remark", SqlDbType.NVarChar,255),
                    new SqlParameter("@parent_mobile", SqlDbType.NVarChar,20)};
						
			parameters[0].Value = model.order_no;            			
			parameters[1].Value = model.user_id;            			
			parameters[2].Value = model.group_id;            			
			parameters[3].Value = model.amount;            			
			parameters[4].Value = model.add_time;            			
			parameters[5].Value = model.payment_id;            			
			parameters[6].Value = model.payment_status;            			
			parameters[7].Value = model.payment_time;            			
			parameters[8].Value = model.trade_no;
            parameters[9].Value = model.remark;
            parameters[10].Value = model.parent_mobile;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(Model.member_order model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update ch_member_order set ");
			                                                
            strSql.Append("order_no = @order_no,");                                    
            strSql.Append("user_id = @user_id,");                                    
            strSql.Append("group_id = @group_id,");                                    
            strSql.Append("amount = @amount,");                                    
            strSql.Append("add_time = @add_time,");                                    
            strSql.Append("payment_id = @payment_id,");                                    
            strSql.Append("payment_status = @payment_status,");                                    
            strSql.Append("payment_time = @payment_time,");                                    
            strSql.Append("trade_no = @trade_no");
			strSql.Append(" where id=@id");
			SqlParameter[] parameters = {
			            new SqlParameter("@id", SqlDbType.Int,4) ,            
                        new SqlParameter("@order_no", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@user_id", SqlDbType.Int,4) ,            
                        new SqlParameter("@group_id", SqlDbType.Int,4) ,            
                        new SqlParameter("@amount", SqlDbType.Decimal,5) ,            
                        new SqlParameter("@add_time", SqlDbType.DateTime) ,            
                        new SqlParameter("@payment_id", SqlDbType.TinyInt,1) ,            
                        new SqlParameter("@payment_status", SqlDbType.TinyInt,1) ,            
                        new SqlParameter("@payment_time", SqlDbType.DateTime) ,            
                        new SqlParameter("@trade_no", SqlDbType.NVarChar,100)             
            };
						            
            parameters[0].Value = model.id;                        
            parameters[1].Value = model.order_no;                        
            parameters[2].Value = model.user_id;                        
            parameters[3].Value = model.group_id;                        
            parameters[4].Value = model.amount;                        
            parameters[5].Value = model.add_time;                        
            parameters[6].Value = model.payment_id;                        
            parameters[7].Value = model.payment_status;                        
            parameters[8].Value = model.payment_time;                        
            parameters[9].Value = model.trade_no;                        
            int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		
		/// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ch_member_order ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
		
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.member_order GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 id,order_no,user_id,group_id,amount,add_time,payment_id,payment_status,payment_time,trade_no,remark,parent_mobile");
            strSql.Append(" from ch_member_order");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            Model.member_order model = new Model.member_order();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据订单号返回一个实体
        /// </summary>
        public Model.member_order GetModel(string order_no)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 id,order_no,user_id,group_id,amount,add_time,payment_id,payment_status,payment_time,trade_no,remark,parent_mobile");
            strSql.Append(" from ch_member_order");
            strSql.Append(" where order_no=@order_no");
            SqlParameter[] parameters = {
					new SqlParameter("@order_no", SqlDbType.NVarChar,100)};
            parameters[0].Value = order_no;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,order_no,user_id,group_id,amount,add_time,payment_id,payment_status,payment_time,trade_no,remark,parent_mobile");
            strSql.Append(" FROM ch_member_order ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }
        
        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM ch_member_order");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion
        
        #region 扩展方法============================
        /// <summary>
        /// 返回标题名称
        /// </summary>
        public string GetTitle(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 title from ch_member_order");
            strSql.Append(" where id=" + id);
            string title = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(title))
            {
                return "-";
            }
            return title;
        }

        /// <summary>
        /// 根据订单号获取支付方式ID
        /// </summary>
        public int GetPaymentId(string order_no)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 payment_id from ch_member_order");
            strSql.Append(" where order_no=@order_no");
            SqlParameter[] parameters = {
                    new SqlParameter("@order_no", SqlDbType.NVarChar,100)};
            parameters[0].Value = order_no;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            return 0;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public void UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ch_member_order set " + strValue);
            strSql.Append(" where id=" + id);
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }
        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(string order_no, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ch_member_order set " + strValue);
            strSql.Append(" where order_no='" + order_no + "'");
            int rowsAffected = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.member_order DataRowToModel(DataRow row)
        {
            Model.member_order model = new Model.member_order();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["order_no"] != null)
                {
                    model.order_no = row["order_no"].ToString();
                }
                if (row["user_id"] != null && row["user_id"].ToString() != "")
                {
                    model.user_id = int.Parse(row["user_id"].ToString());
                }
                if (row["group_id"] != null && row["group_id"].ToString() != "")
                {
                    model.group_id = int.Parse(row["group_id"].ToString());
                }
                if (row["amount"] != null && row["amount"].ToString() != "")
                {
                    model.amount = decimal.Parse(row["amount"].ToString());
                }
                if (row["add_time"] != null && row["add_time"].ToString() != "")
                {
                    model.add_time = DateTime.Parse(row["add_time"].ToString());
                }
                if (row["payment_id"] != null && row["payment_id"].ToString() != "")
                {
                    model.payment_id = int.Parse(row["payment_id"].ToString());
                }
                if (row["payment_status"] != null && row["payment_status"].ToString() != "")
                {
                    model.payment_status = int.Parse(row["payment_status"].ToString());
                }
                if (row["payment_time"] != null && row["payment_time"].ToString() != "")
                {
                    model.payment_time = DateTime.Parse(row["payment_time"].ToString());
                }
                if (row["trade_no"] != null)
                {
                    model.trade_no = row["trade_no"].ToString();
                }
                if (row["remark"] != null)
                {
                    model.remark = row["remark"].ToString();
                }
                if (row["parent_mobile"] != null)
                {
                    model.parent_mobile = row["parent_mobile"].ToString();
                }
            }
            return model;
        }


        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetCompanyList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append("ch_company.title com_title,ch_company.id com_id,ch_company.mobile com_mobile,");
            strSql.Append(" ch_member_order.id,ch_member_order.order_no,ch_member_order.user_id,ch_member_order.group_id,ch_member_order.amount,ch_member_order.add_time,ch_member_order.payment_id,ch_member_order.payment_status,ch_member_order.payment_time,ch_member_order.trade_no,ch_member_order.remark,ch_member_order.parent_mobile");
            strSql.Append(" FROM ch_member_order left join ch_compay  on   ch_member_order.user_id=ch_company.user_id");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetListForExport(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }

            strSql.Append(" order_no as '订单号',");
            strSql.Append("b.user_name as '用户名',");
            strSql.Append("b.mobile as '手机号',");
            strSql.Append("c.title as '会员级别',");
            strSql.Append("a.amount as '金额',");
            strSql.Append("case a.payment_status when 1 then '支付成功' else  '未支付' end as '支付状态',");
            strSql.Append("a.parent_mobile as '推荐人',");
            strSql.Append("a.add_time as '提交时间',");
            strSql.Append("a.payment_time as '支付时间'");


            strSql.Append(" from ch_member_order a");
            strSql.Append(" left join dt_users b on a.user_id = b.id");
            strSql.Append(" left join dt_user_groups c on a.group_id = c.id");
          
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        #endregion
    }
}
